package com.base.cn.platform.os.controller.truncate;

import com.base.cn.platform.os.common.controller.BaseController;
import com.base.cn.platform.os.common.utils.ObjectUtils;
import com.base.cn.platform.os.common.utils.StringUtils;
import com.base.cn.platform.os.entity.manage.config.SysConfig;
import com.base.cn.platform.os.service.manage.config.SysConfigBiz;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

/**
 * 清空数据
 *
 * @author s.li
 * @date 2019/02/22
 */
@RestController
@RequestMapping("/manage/truncate")
public class DBTruncateController extends BaseController {
    @Autowired
    private Environment environment;
    @Autowired
    private SysConfigBiz sysConfigBiz;
    /**
     * 清空数据库中数据接口
     */
    @RequestMapping("/truncateDB")
    public String truncateDB(){
        Connection courseConn = null;
        Connection examConn = null;
        Connection baseConn = null;

        String driverClassName = environment.getProperty("spring.datasource.driver-class-name");

        String courseUrl = environment.getProperty("course-service.spring.datasource.url");
        String courseName = environment.getProperty("course-service.spring.datasource.username");
        String coursePassword = environment.getProperty("course-service.spring.datasource.password");
        String courseExcludeTables = environment.getProperty("course-service.exclude.truncate.tableNames");
        Set<String> courseExcludeTableNameSet = new HashSet<>();
        if(StringUtils.isNotEmpty(courseExcludeTables)){
            Arrays.stream(courseExcludeTables.split(",")).forEach(name->{
                if(StringUtils.isNotEmpty(name)){
                    courseExcludeTableNameSet.add(name.trim());
                }
            });
        }

        String examUrl = environment.getProperty("exam-service.spring.datasource.url");
        String examName = environment.getProperty("exam-service.spring.datasource.username");
        String examPassword = environment.getProperty("exam-service.spring.datasource.password");
        String examExcludeTables = environment.getProperty("exam-service.exclude.truncate.tableNames");
        Set<String> examExcludeTableNameSet = new HashSet<>();
        if(StringUtils.isNotEmpty(examExcludeTables)){
            Arrays.stream(examExcludeTables.split(",")).forEach(name->{
                if(StringUtils.isNotEmpty(name)){
                    examExcludeTableNameSet.add(name.trim());
                }
            });
        }

        String baseUrl = environment.getProperty("base-service.spring.datasource.url");
        String baseName = environment.getProperty("base-service.spring.datasource.username");
        String basePassword = environment.getProperty("base-service.spring.datasource.password");
        String baseExcludeTables = environment.getProperty("base-service.exclude.truncate.tableNames");
        Set<String> baseExcludeTableNameSet = new HashSet<>();
        if(StringUtils.isNotEmpty(baseExcludeTables)){
            Arrays.stream(baseExcludeTables.split(",")).forEach(name->{
                if(StringUtils.isNotEmpty(name)){
                    baseExcludeTableNameSet.add(name.trim());
                }
            });
        }
        try {
            Class.forName(driverClassName);// 动态加载mysql驱动
            // 一个Connection代表一个数据库连接
            courseConn = DriverManager.getConnection(courseUrl,courseName,coursePassword);

            DatabaseMetaData courseMetaData = courseConn.getMetaData();
            List<String> courseTables = new ArrayList<>();
            ResultSet courseResultSet = courseMetaData.getTables(null,"%","%",new String[]{"TABLE"});
            while (courseResultSet.next()){
                String tableName = courseResultSet.getString("TABLE_NAME");
                courseTables.add(tableName);
            }
            examConn = DriverManager.getConnection(examUrl,examName,examPassword);
            DatabaseMetaData examMetaData = examConn.getMetaData();
            List<String> examTables = new ArrayList<>();
            ResultSet examResultSet = examMetaData.getTables(null,"%","%",new String[]{"TABLE"});
            while (examResultSet.next()){
                String tableName = examResultSet.getString("TABLE_NAME");
                examTables.add(tableName);
            }

            baseConn = DriverManager.getConnection(baseUrl,baseName,basePassword);
            DatabaseMetaData baseMetaData = baseConn.getMetaData();
            List<String> baseTables = new ArrayList<>();
            ResultSet baseResultSet = baseMetaData.getTables(null,"%","%",new String[]{"TABLE"});
            while (baseResultSet.next()){
                String tableName = baseResultSet.getString("TABLE_NAME");
                baseTables.add(tableName);
            }
            List<String> courseSqlList = new ArrayList<>();
            addSql(courseTables,courseExcludeTableNameSet,courseSqlList);

            List<String> examSqlList = new ArrayList<>();
            addSql(examTables,examExcludeTableNameSet,examSqlList);

            List<String> baseSqlList = new ArrayList<>();
            addSql(baseTables,baseExcludeTableNameSet,baseSqlList);

            Statement courseStmt = courseConn.createStatement();
            Statement examStmt = examConn.createStatement();
            Statement baseStmt = baseConn.createStatement();

            if(ObjectUtils.isNotEmpty(courseSqlList)){
                courseSqlList.forEach(sql->{
                    execute(courseStmt,sql);
                });
            }
            if(ObjectUtils.isNotEmpty(examSqlList)){
                examSqlList.forEach(sql->{
                    execute(examStmt,sql);
                });
            }
            if(ObjectUtils.isNotEmpty(baseSqlList)){
                baseSqlList.forEach(sql->{
                    execute(baseStmt,sql);
                });
            }
            Statement baseStatement = baseConn.createStatement();
            //删除超级管理员之外的用户
            baseStatement.executeUpdate("delete from sysuser where id > 1");
            //删除超级管理员之外的角色与用户关联数据
            baseStatement.executeUpdate("delete from sysuserrole where userId > 1");
            //删除角色
            baseStatement.executeUpdate("delete from sysrole where id > 3 and id != 5");
            //删除角色与权限的关联
            baseStatement.executeUpdate("delete from sysrolefunction where roleId > 3  and roleId !=5");

            //清空平台网站信息配置
            TreeMap<String, String> treeMap = new TreeMap<>();
            treeMap.put("appName", "");
            treeMap.put("author", "");
            treeMap.put("company", "©2010-{year}");
            treeMap.put("description", "©2010-{year}");
            treeMap.put("email", "");
            treeMap.put("keywords", "");
            treeMap.put("phone", "");
            treeMap.put("tagline", "");
            treeMap.put("title", "");
            treeMap.put("workTime", "9:00-18:00");
            String context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 4");

            //清空服务邮箱号配置
            treeMap = new TreeMap<>();
            treeMap.put("emailHost", "");
            treeMap.put("emailPassword", "");
            treeMap.put("emailUsername", "");
            treeMap.put("port", "");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 17");

            //清空服务短信接口配置
            treeMap = new TreeMap<>();
            treeMap.put("AppKey", "");
            treeMap.put("appId", "");
            treeMap.put("appSecret", "");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 18");

            //清空96kr云视频配置
            SysConfig sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(19));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("c_auth_id","");
            treeMap.put("c_auth_key","");
            treeMap.put("customerKey","");
            treeMap.put("i_auth_id","");
            treeMap.put("i_auth_key","");
            treeMap.put("p_auth_id","");
            treeMap.put("p_auth_key","");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 19");

            //清空客服系统链接配置
            treeMap = new TreeMap<>();
            treeMap.put("context", "");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 20");

            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(21));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("alipayKey","");
            treeMap.put("alipaypartnerID","");
            treeMap.put("appSecret","");
            treeMap.put("appid","");
            treeMap.put("applyAppId","");
            treeMap.put("applyAppSecret","");
            treeMap.put("mch_id","");
            treeMap.put("openAppAppId","");
            treeMap.put("openMchId","");
            treeMap.put("openPayKey","");
            treeMap.put("payKey","");
            treeMap.put("privateKey","");
            treeMap.put("publicKey","");
            treeMap.put("sellerEmail","");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 21");

            //清空第三方统计代码配置
            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(22));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("code", "");
            treeMap.put("mateCode", "");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 22");


            //清空微信小程序配置
            treeMap = new TreeMap<>();
            treeMap.put("appId", "");
            treeMap.put("secret", "");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 23");

            //清空QQ登录配置
            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(24));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("appId","");
            treeMap.put("appKey","");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 24");

            //清空微信登录配置
            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(25));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("appId","");
            treeMap.put("appSecret","");
            treeMap.put("pcOpenAppId","");
            treeMap.put("pcOpenSecret","");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 25");

            //清空微信登录配置
            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(28));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("appSdkSign","");
            treeMap.put("ccAppId","");
            treeMap.put("ccAppKey","");
            treeMap.put("liveAppKey","");
            treeMap.put("plUserId","");
            treeMap.put("readToken","");
            treeMap.put("secretKey","");
            treeMap.put("writeToken","");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 28");

            //设置后台可提前进入直播时间
            sysConfig = sysConfigBiz.getSysConfigById(new BigDecimal(31));
            treeMap = sysConfigBiz.contextToMap(sysConfig.getContext());
            treeMap.put("liveTime","45");
            context = gson.toJson(treeMap);
            baseStatement.executeUpdate("update sysconfig set context ='"+context+"'  where id = 31");

            Statement courseStatement = courseConn.createStatement();
            //删除默认数据之外的数据
            courseStatement.executeUpdate("delete from classestopictype where id > 5");

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if(courseConn!=null){
                    courseConn.close();
                }
                if(examConn!=null){
                    examConn.close();
                }
                if(baseConn!=null){
                    baseConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return "数据清除成功";
    }

    private void addSql(List<String> tableList,Set<String> excludeTableNameSet,List<String> sqlList){
        tableList.forEach(name->{
            if(ObjectUtils.isNotEmpty(excludeTableNameSet)){
                if(!excludeTableNameSet.contains(name)){
                    sqlList.add(" truncate table `"+name+"`");
                }
            }else{
                sqlList.add(" truncate table `"+name+"`");
            }
        });
    }

    private boolean execute(Statement statement, String sql){
        try {
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
